﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data.OracleClient;
using Maticsoft.DBUtility;

namespace Xiaov.SmartWeb.Application.Excel
{
  public class ExportExcel
  {
    private Dictionary<string, CODE_BBBMB> bbbmbDic = null;//报表编码表hash，为了提高速度
    private Dictionary<string, XMLVERSION> xmlversionDic = null;// xml版本hash
    private Dictionary<string, string> dwmcDic = null;// 单位名称

    //返回xml，外部调用方法
    public string GetXML(string dwbm, string bbbmList, string nf, string yf)
    {
      InitBBBMB(nf, yf, dwbm);
      InitXmlversion(nf, yf, dwbm);
      GetDWMC();
      StringBuilder xml = new StringBuilder();
      xml.Append(GetXMLWorkBookStart());
      xml.Append(GetXMLWorkSheet(dwbm, bbbmList, nf, yf));
      xml.Append(GetXMLWorkBookEnd());
      return xml.ToString();
    }
    //初始化bbbmb至hash
    private void InitBBBMB(string nf, string yf, string dwbm)
    {
      bbbmbDic = new Dictionary<string, CODE_BBBMB>();
      string sql = "select a.* from code_bbbmb a,xmlversion_dw_nf_yf b where a.bbbm=b.bbbm and nf="
        + nf + " and yf=" + yf + " and dwbm='" + dwbm + "'";
      OracleDataReader dr = DbHelperOra.ExecuteReader(sql);
      while (dr.Read())
      {
        CODE_BBBMB model = new CODE_BBBMB();
        model.BBBM = dr["bbbm"].ToString();
        model.BBWH = dr["bbwh"].ToString();
        model.BBMC = dr["bbmc"].ToString();
        bbbmbDic.Add(model.BBBM, model);
      }
      dr.Close();

    }
    //初始化xmlversion
    private void InitXmlversion(string nf, string yf, string dwbm)
    {
      xmlversionDic = new Dictionary<string, XMLVERSION>();
      string sql = "select * from xmlversion_dw_nf_yf where nf=" + nf + " and yf=" + yf + " and dwbm='" + dwbm + "'";
      OracleDataReader dr = DbHelperOra.ExecuteReader(sql);
      while (dr.Read())
      {
        XMLVERSION model = new XMLVERSION();
        model.BBBM = dr["bbbm"].ToString();
        model.CAPTION = dr["caption"].ToString();
        model.COORDINATES = dr["coordinates"].ToString();
        model.ROWCOUNT = int.Parse(dr["rowcount"].ToString());
        model.COLUMNCOUNT = int.Parse(dr["columncount"].ToString());

        xmlversionDic.Add(model.BBBM, model);
      }
      dr.Close();
    }
    //xml开头
    private string GetXMLWorkBookStart()
    {
      string xml = @"<?xml version=""1.0""?>
<?mso-application progid=""Excel.Sheet""?>
<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""
 xmlns:o=""urn:schemas-microsoft-com:office:office""
 xmlns:x=""urn:schemas-microsoft-com:office:excel""
 xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""
 xmlns:html=""http://www.w3.org/TR/REC-html40"">
 <DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">
  <Created>1996-12-17T01:32:42Z</Created>
  <LastSaved>2013-12-23T08:51:20Z</LastSaved>
  <Version>11.9999</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns=""urn:schemas-microsoft-com:office:office"">
  <RemovePersonalInformation/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">
  <WindowHeight>4530</WindowHeight>
  <WindowWidth>8505</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <AcceptLabelsInFormulas/>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID=""Default"" ss:Name=""Normal"">
   <Alignment ss:Vertical=""Bottom""/>
   <Borders/>
   <Font ss:FontName=""宋体"" x:CharSet=""134"" ss:Size=""12""/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
    <Style ss:ID=""s21"">
      <Borders>
        <Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>
        <Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>
        <Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>
        <Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>
      </Borders>
    </Style>
<Style ss:ID=""s24"">
   <Alignment ss:Horizontal=""Center"" ss:Vertical=""Center""/>
   <Font ss:FontName=""宋体"" x:CharSet=""134"" ss:Size=""18"" ss:Bold=""1""/>
   <Interior/>
   <NumberFormat ss:Format=""@""/>
   <Protection/>
  </Style>
 </Styles>";
      return xml;
    }
    //xml结尾
    private string GetXMLWorkBookEnd()
    {
      return "</Workbook>";
    }
    //获取sheetxml
    private string GetXMLWorkSheet(string dwbm, string bbbmList, string nf, string yf)
    {
      StringBuilder xml = new StringBuilder();
      string[] bbbm = bbbmList.Split(new char[] { ',' });
      for (int i = 0; i < bbbm.Length; i++)
      {
        if (bbbmbDic.ContainsKey(bbbm[i]))
        {
          xml.Append(GetXMLSheetStart(bbbm[i]));
          xml.Append(GetXMLSheetTitle(bbbm[i], dwbm, nf, yf));
          xml.Append(GetXMLSheetHeader(bbbm[i]));
          xml.Append(GetXMLSheetContent(dwbm, bbbm[i], nf, yf));
          xml.Append(GetXMLSheetEnd());
        }
      }
      return xml.ToString();
    }
    //获取sheet开始
    private string GetXMLSheetStart(string bbbm)
    {
      StringBuilder xml = new StringBuilder();
      CODE_BBBMB model_bbbmb = bbbmbDic[bbbm];
      XMLVERSION model_xmlversion = xmlversionDic[bbbm];
      xml.Append(@"<Worksheet ss:Name=""" + model_bbbmb.BBWH + model_bbbmb.BBMC + model_bbbmb.BBBM.Substring(2, 4) + @""">");
      xml.Append(@"<Table ss:ExpandedColumnCount=""" + model_xmlversion.COLUMNCOUNT
        + @""" ss:ExpandedRowCount=""1000"" x:FullColumns=""1"" x:FullRows=""1""
          ss:DefaultColumnWidth=""54"" ss:DefaultRowHeight=""14.25"">");
      xml.Append("<Column ss:Index=\"1\" ss:Hidden=\"1\" ss:AutoFitWidth=\"0\"/>");
      xml.Append("<Column ss:Index=\"2\" ss:Hidden=\"1\" ss:AutoFitWidth=\"0\"/>");
      xml.Append("<Column ss:Index=\"3\" ss:Hidden=\"1\" ss:AutoFitWidth=\"0\"/>");
      xml.Append("<Column ss:Index=\"4\" ss:Hidden=\"1\" ss:AutoFitWidth=\"0\"/>");
      xml.Append(" <Column ss:Index=\"5\" ss:Width=\"280\"/>");
      xml.Append("<Column ss:Index=\"6\" ss:Hidden=\"1\" ss:AutoFitWidth=\"0\"/>");
      xml.Append("<Column ss:Index=\"7\" ss:Hidden=\"1\" ss:AutoFitWidth=\"0\"/>");
      xml.Append("<Column ss:Index=\"10\" ss:Hidden=\"1\" ss:AutoFitWidth=\"0\"/>");
      xml.Append("<Column ss:Index=\"11\" ss:Hidden=\"1\" ss:AutoFitWidth=\"0\"/>");
      xml.Append("<Column ss:Index=\"12\" ss:Hidden=\"1\" ss:AutoFitWidth=\"0\"/>");
      xml.Append("<Column ss:Index=\"13\" ss:Hidden=\"1\" ss:AutoFitWidth=\"0\"/>");
      xml.Append("<Column ss:Index=\"" + model_xmlversion.COLUMNCOUNT.ToString() + "\" ss:Hidden=\"1\" ss:AutoFitWidth=\"0\"/>");
      return xml.ToString();
    }
    //获取sheet结束
    private string GetXMLSheetEnd()
    {
      string xml = @"
  </Table>
  <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">
   <Unsynced/>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>4</ActiveRow>
     <ActiveCol>3</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>";
      return xml;
    }
    //获取sheet内容部分xml
    private string GetXMLSheetContent(string dwbm, string bbbm, string nf, string yf)
    {
      StringBuilder xml = new StringBuilder();
      CODE_BBBMB model_bbbmb = bbbmbDic[bbbm];
      XMLVERSION model_xmlversion = xmlversionDic[bbbm];
      Dictionary<string, ENCO_BBZD> columnList = GetColumnList(bbbm);
      string sql = "select * from " + bbbm + " where dwbm='" + dwbm + "' and nf='" + nf + "' and yf='" + yf + "' order by hc";
      OracleDataReader dr = Maticsoft.DBUtility.DbHelperOra.ExecuteReader(sql);
      while (dr.Read())
      {
        xml.Append("<Row ss:AutoFitHeight=\"0\">");
        foreach (KeyValuePair<string, ENCO_BBZD> item in columnList)
        {
          ENCO_BBZD model_zd = item.Value;
          string zdlx = model_zd.SJLX == "字符型" ? "String" : "Number";
          if (model_zd.ZD == "ZBMC")
          {
            xml.Append("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"" + zdlx + "\">"
              + GetZBMC(dr[model_zd.ZD].ToString(), int.Parse(dr["cj"].ToString()))
              + "</Data></Cell>");
          }
          else
          {
            xml.Append("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"" + zdlx + "\">"
              + dr[model_zd.ZD].ToString() + "</Data></Cell>");
          }
        }
        xml.Append(" </Row>");
      }
      dr.Close();
      return xml.ToString();
    }
    //指标名称前加空格根据cj
    private string GetZBMC(string zbmc, int cj)
    {
      string s = zbmc;
      for (int i = 1; i < cj; i++)
      {
        s = "  " + s;
      }
      return s;
    }
    //获取字段列表
    private Dictionary<string, ENCO_BBZD> GetColumnList(string bbbm)
    {
      Dictionary<string, ENCO_BBZD> columnList = new Dictionary<string, ENCO_BBZD>();
      string sql = "select * from enco_bbzd where bbbm='" + bbbm + "' order by zdsx";
      OracleDataReader dr = DbHelperOra.ExecuteReader(sql);
      while (dr.Read())
      {
        ENCO_BBZD model = new ENCO_BBZD();
        model.BBBM = bbbm;
        model.ZD = dr["zd"].ToString();
        model.SJLX = dr["sjlx"].ToString();
        columnList.Add(model.ZD, model);
      }
      dr.Close();
      return columnList;
    }
    //获取sheet表头xml
    private string GetXMLSheetHeader(string bbbm)
    {
      StringBuilder xml = new StringBuilder();
      XMLVERSION model = xmlversionDic[bbbm];

      string[] captionList = model.CAPTION.Split(new char[] { ',' });
      string[] coordinatesList = model.COORDINATES.Split(new char[] { '|' });
      int row = 0;
      xml.Append("<Row ss:AutoFitHeight=\"0\">");

      for (int i = 0; i < coordinatesList.Length; i++)
      {
        string[] sl_Coordinates_Cell = coordinatesList[i].Split(new char[] { ',' });
        int x = Convert.ToInt32(sl_Coordinates_Cell[0]);
        int x_span = Convert.ToInt32(sl_Coordinates_Cell[1]);
        int y = Convert.ToInt32(sl_Coordinates_Cell[2]);
        int y_span = Convert.ToInt32(sl_Coordinates_Cell[3]);
        if (y > row)
        {
          xml.Append("</Row>");
          xml.Append("<Row ss:AutoFitHeight=\"0\">");
          row++;
        }
        xml.Append("<Cell ss:StyleID=\"s21\"");
        xml.Append(" ss:Index=\"" + (x + 1).ToString() + "\" ");
        if ((x_span - 1) > 0)
        { xml.Append(" ss:MergeAcross=\"" + (x_span - 1).ToString() + "\" "); }
        if ((y_span - 1) > 0)
        { xml.Append(" ss:MergeDown=\"" + (y_span - 1).ToString() + "\" "); }
        xml.Append(">");
        xml.Append("<Data ss:Type=\"String\">" + captionList[i] + "</Data>)");
        xml.Append("</Cell>");
      }
      xml.Append("</Row>");
      return xml.ToString();
    }
    //获取sheet通用表头
    private string GetXMLSheetTitle(string bbbm, string dwbm, string nf, string yf)
    {
      StringBuilder xml = new StringBuilder();
      XMLVERSION model = xmlversionDic[bbbm];
      CODE_BBBMB model_bbbm = bbbmbDic[bbbm];
      //标题
      xml.Append("<Row><Cell ss:StyleID=\"s24\" ss:MergeAcross=\"" + (model.COLUMNCOUNT - 1).ToString()
        + "\"><Data ss:Type=\"String\">" + model_bbbm.BBMC + "</Data></Cell></Row>");
      //表号
      xml.Append("<Row>");
      for (int i = 0; i < model.COLUMNCOUNT; i++)
      {
        if (i == model.COLUMNCOUNT - 4)
        {
          xml.Append("<Cell ss:MergeAcross=\"1\"><Data ss:Type=\"String\">表   号：" + model_bbbm.BBWH + "</Data></Cell>");
          i++;
        }
        else
        {
          xml.Append("<Cell />");
        }
      }
      xml.Append("</Row>");
      //制表机关
      xml.Append("<Row>");
      for (int i = 0; i < model.COLUMNCOUNT; i++)
      {
        if (i == model.COLUMNCOUNT - 4)
        {
          xml.Append("<Cell ss:MergeAcross=\"1\"><Data ss:Type=\"String\">制表机关：华北石油工程有限公司</Data></Cell>");
          i++;
        }
        else
        {
          xml.Append("<Cell />");
        }
      }
      xml.Append("</Row>");
      //文号
      xml.Append("<Row>");
      for (int i = 0; i < model.COLUMNCOUNT; i++)
      {
        if (i == 4)
        {
          xml.Append("<Cell><Data ss:Type=\"String\">编制单位：" + dwmcDic[dwbm] + "</Data></Cell>");
        }
        else if (i == 14)
        {
          xml.Append("<Cell><Data ss:Type=\"String\">" + nf + "年" + yf + "月</Data></Cell>");
        }
        else if (i == model.COLUMNCOUNT - 4)
        {
          xml.Append("<Cell ss:MergeAcross=\"1\"><Data ss:Type=\"String\">文号：**" + nf + "**</Data></Cell>");
          i++;
        }
        else
        {
          xml.Append("<Cell />");
        }
      }
      xml.Append("</Row>");
      return xml.ToString();
    }
    //单位名称
    private void GetDWMC()
    {
      dwmcDic = new Dictionary<string, string>();
      string sql = "select * from dwbmb ";
      OracleDataReader dr = DbHelperOra.ExecuteReader(sql);
      while (dr.Read())
      {
        dwmcDic.Add(dr["dwbm"].ToString(), dr["dwmc"].ToString());
      }
      dr.Close();
    }
  }
}